/**
 * @description: 
 * @version: 0.0.1
 * @author: ljr
 * @Date: 2023-09-13 14:07:35
 */

import { Provide } from '@midwayjs/core';
import { OrdRecord } from './orders_records.entity';
import { InjectRepository, InjectDataSource } from '@midwayjs/sequelize';
import { Repository, Sequelize } from 'sequelize-typescript';

@Provide()
export class OrdersRecordsModel {
  @InjectRepository(OrdRecord)
  OrdRecordRepository: Repository<OrdRecord>;

  @InjectDataSource()
  executeRawSQL: Sequelize;
  /**
   * 根据id查询订单数量
   * @param {*} id 
   * @returns 
   */
  async getCountById(id: number): Promise<any> {
    const sql = `SELECT count(1) as total FROM orders_records WHERE id = ${id} and is_del = 0;`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 根据归属日期查询订单数量
   * @param {*} belongDate 
   * @returns 
   */
  async getCountByDate(belongDate: string): Promise<any> {
    const sql = `SELECT count(1) as total FROM orders_records WHERE belong_date = '${belongDate}' and is_del = 0;`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 新增订单信息
   * @param {*} orderName 订单名称 
   * @param {*} orderDetails 订单详情 
   * @param {*} orderId 订单编号 
   * @param {*} remarks 订单备注
   * @param {*} belongDate 归属日期
   * @param {*} createdUserid 创建人id
   * @param {*} createdUsername 创建人名称
   * @param {*} brandName 品牌名称
   * @returns 
   */
  async insertOrders(params: any): Promise<any> {
    const sql = `INSERT INTO orders_records 
    SET order_name = '${params.orderName}',
      order_details = '${params.orderDetails}',
      order_id = '${params.orderId}',
      remarks = '${params.remarks}',
      belong_date = '${params.belongDate}',
      created_userid = '${params.createdUserid}',
      create_username = '${params.createdUsername}',
      brand_name = '${params.brandName}',
      created_at = now(),
      updated_at = now();`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 分页查询获取总条数
   * @param {*} searchKey   搜索信息
   * @returns 
   */
  async getTotalNum(searchKey: any): Promise<any> {
    let sql = `SELECT count(1) as num from orders_records where is_del = 0;`;
    if (searchKey) {
      sql = `SELECT count(1) AS num FROM orders_records AS o 
    WHERE o.is_del = 0 AND ( o.order_name LIKE '%${searchKey}%' 
        OR o.order_details LIKE '%${searchKey}%' 
        OR o.order_id LIKE '%${searchKey}%' 
        OR o.remarks LIKE '%${searchKey}%' 
        OR o.create_username LIKE '%${searchKey}%');`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 分页查询获取列表
   * @param {*} pageNum 
   * @param {*} pageSize 
   * @param {*} searchKey 
   * @returns 
   */
  async getDataByPage(pageNum: number, pageSize: number, searchKey: any): Promise<any> {
    const _limit = (pageNum - 1) * pageSize;
    let sql = `SELECT id, order_name, order_details, order_id,remarks, belong_date, created_userid,
    create_username, created_at, updated_at, brand_name,
    chuanyun_object_id,chuanyun_owner_unionid,chuanyun_owner_name,chuanyun_start_time,chuanyun_end_time,chuanyun_pm_name,chuanyun_pm_unionid
FROM
    orders_records where is_del = 0
ORDER BY created_at DESC LIMIT ${_limit},${pageSize};`;
    if (searchKey) {
      sql = `SELECT id, order_name,order_details, order_id, remarks,
  belong_date, created_userid, create_username,created_at, updated_at, brand_name,
  chuanyun_object_id,chuanyun_owner_unionid,chuanyun_owner_name,chuanyun_start_time,chuanyun_end_time,chuanyun_pm_name,chuanyun_pm_unionid
  FROM orders_records
  WHERE is_del = 0 AND ( order_name LIKE '%${searchKey}%' 
      OR order_id LIKE '%${searchKey}%' 
      OR order_details LIKE '%${searchKey}%' 
      OR remarks LIKE '%${searchKey}%' 
      OR create_username LIKE '%${searchKey}%') 
  ORDER BY created_at DESC LIMIT ${_limit},${pageSize};`;
    }

    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 根据id删除订单数据
   * @param {*} id 
   * @returns 
   */
  async delDataById(id: number): Promise<any> {
    const sql = `update orders_records set is_del = 1 where id = ${id}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
  * 根据id修改订单数据
  * @param {*} id 
  * @param {*} orderName 
  * @param {*} orderDetails 
  * @param {*} remarks 
  * @param {*} orderId 
  * @param {*} brandName 
  * @returns 
  */
  async updateDataById(params: any): Promise<any> {
    const sql = `UPDATE orders_records 
    SET order_name = '${params.orderName}',
        order_details = '${params.orderDetails}',
        remarks = '${params.remarks}',
        order_id = '${params.orderId}',
        brand_name = '${params.brandName}',
        updated_at = now()
    WHERE
        id = ${params.id}`;

    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 根据id查询订单数据
   * @param {*} id
   * @returns 
   */
  async getDataById(id: number): Promise<any> {
    const sql = `select id,order_name,order_details,order_id,remarks,belong_date,created_userid,create_username,created_at,updated_at,brand_name,
    chuanyun_object_id,chuanyun_owner_unionid,chuanyun_owner_name,chuanyun_start_time,chuanyun_end_time,chuanyun_pm_name,chuanyun_pm_unionid 
    from orders_records WHERE id = ${id} and is_del = 0;`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

   /**
   * 氚云导入 创建订单信息信息
   * @param {*} chuanyun_object_id    氚云订单记录ObjectId
   * @param {*} chuanyun_owner_name    拥有者
   * @param {*} chuanyun_owner_unionid    拥有者钉钉unionid
   * @param {*} order_name    订单名称
   * @param {*} chuanyun_start_time    启动日期
   * @param {*} chuanyun_end_time    完结日期
   * @param {*} chuanyun_pm_name    主PM
   * @param {*} chuanyun_pm_unionid    主PM钉钉unionid
   * @param {*} order_id    订单id
   * @param {*} brand_name    品牌名称
   * @returns 
   */
   async chuanyunInsertOrder(params: any): Promise<any> {
    return await this.OrdRecordRepository.create(
      {
        chuanyun_object_id: params.chuanyun_object_id,
        chuanyun_owner_name: params.chuanyun_owner_name,
        chuanyun_owner_unionid: params.chuanyun_owner_unionid,
        order_name: params.order_name,
        chuanyun_start_time: params.chuanyun_start_time,
        chuanyun_end_time: params.chuanyun_end_time,
        chuanyun_pm_name: params.chuanyun_pm_name,
        chuanyun_pm_unionid: params.chuanyun_pm_unionid,
        order_id: params.order_id,
        brand_name: params.brand_name,
        created_at: new Date(),
        updated_at: new Date()
      }
    );
  }

  /**
   * 查询当前氚云订单是否已导入
   * @param id 
   */
  async chuanyunGetOrderByid(order_id: string): Promise<any> {
    const sql = `select count(1) as total from orders_records where order_id = '${order_id}'`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

   /**
   * 氚云导入 更新订单信息
   * @param {*} chuanyun_object_id    氚云订单记录ObjectId
   * @param {*} chuanyun_owner_name    拥有者
   * @param {*} chuanyun_owner_unionid    拥有者钉钉unionid
   * @param {*} order_name    订单名称
   * @param {*} chuanyun_start_time    启动日期
   * @param {*} chuanyun_end_time    完结日期
   * @param {*} chuanyun_pm_name    主PM
   * @param {*} chuanyun_pm_unionid    主PM钉钉unionid
   * @param {*} order_id    订单id
   * @param {*} brand_name    品牌名称
   * @returns 
   */
  async chuanyunUpdateOrder(params: any): Promise<any> {
    return await this.OrdRecordRepository.update(
      {
        chuanyun_owner_name: params.chuanyun_owner_name,
        chuanyun_owner_unionid: params.chuanyun_owner_unionid,
        order_name: params.order_name,
        chuanyun_start_time: params.chuanyun_start_time,
        chuanyun_end_time: params.chuanyun_end_time,
        chuanyun_pm_name: params.chuanyun_pm_name,
        chuanyun_pm_unionid: params.chuanyun_pm_unionid,
        order_id: params.order_id,
        brand_name: params.brand_name,
        updated_at: new Date()
      },
      {
        where: { chuanyun_object_id: params.chuanyun_object_id },
      }
    );
  }
}